import pandas as pd
import numpy as np

# 读取CSV文件
login_logs = pd.read_csv('login_logs.csv')
operation_logs = pd.read_csv('operation_logs.csv')
payment_logs = pd.read_csv('payment_logs.csv')
# 统计各字段缺失比例
def missing_ratio(df):
    return df.isnull().mean().round(4) * 100

print("登录日志缺失比例:\n", missing_ratio(login_logs))
print("商品操作日志缺失比例:\n", missing_ratio(operation_logs))
print("支付日志缺失比例:\n", missing_ratio(payment_logs))

# login_logs: device字段缺失用"未知设备"填充
login_logs['device'] = login_logs['device'].fillna('未知设备')

# operation_logs: op_duration缺失用中位数填充
operation_logs['op_duration'] = operation_logs['op_duration'].fillna(operation_logs['op_duration'].median())

# payment_logs: 移除amount缺失行
payment_logs = payment_logs.dropna(subset=['amount'])
# 删除完全重复行
login_logs = login_logs.drop_duplicates()
operation_logs = operation_logs.drop_duplicates()
payment_logs = payment_logs.drop_duplicates()

# login_logs: 去重user_id和login_time重复记录（保留第一条）
login_logs = login_logs.drop_duplicates(subset=['user_id', 'login_time'], keep='first')
# operation_logs: op_duration异常值处理
operation_logs['op_duration'] = operation_logs['op_duration'].clip(lower=0, upper=3600)

# payment_logs: 删除amount≤0的记录
payment_logs = payment_logs[payment_logs['amount'] > 0]
# 取用户最新登录设备
login_latest = login_logs.sort_values('login_time').drop_duplicates('user_id', keep='last')[['user_id', 'device']]

# 合并支付日志与最新登录设备（保留所有支付记录）
payment_merged = pd.merge(
    payment_logs,
    login_latest,
    on='user_id',
    how='left'
)
payment_merged['device'] = payment_merged['device'].fillna('未登录')
# 转换操作类型为行为类型
operation_logs['behavior_type'] = operation_logs['op_type'].replace({
    '加入购物车': '意向操作',
    '收藏': '意向操作',
    '浏览': '浏览'
})

# 构建behavior_logs
behavior_logs = operation_logs[['user_id', 'op_time', 'behavior_type']].rename(columns={'op_time': 'time'})
# 转换login_time为datetime格式（假设格式混乱问题已通过pd.to_datetime处理）
login_logs['login_time'] = pd.to_datetime(login_logs['login_time'], errors='coerce')

# 按device分组统计登录次数
device_login_count = login_logs.groupby('device').size().reset_index(name='login_count')

# 计算用户平均登录间隔（需按user_id排序后计算时间差）
login_logs_sorted = login_logs.sort_values(['user_id', 'login_time'])
login_logs_sorted['login_interval'] = login_logs_sorted.groupby('user_id')['login_time'].diff().dt.total_seconds() / 3600  # 间隔（小时）
device_avg_interval = login_logs_sorted.groupby('device')['login_interval'].mean().reset_index(name='avg_login_interval_hours')

# 合并结果
device_analysis = pd.merge(device_login_count, device_avg_interval, on='device', how='left')
# 筛选支付成功记录
payment_success = payment_logs[payment_logs['pay_status'] == '成功']

# 按user_id聚合计算指标
user_payment_stats = payment_success.groupby('user_id').agg(
    total_amount=('amount', 'sum'),
    success_count=('order_id', 'count'),
    avg_amount=('amount', 'mean')
).reset_index()
# login_logs: 统一login_time为"yyyy-mm-dd hh:mm:ss"
login_logs['login_time'] = pd.to_datetime(login_logs['login_time'], errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S')

# payment_logs: 转换pay_time为标准格式
payment_logs['pay_time'] = pd.to_datetime(payment_logs['pay_time'], format='%m/%d/%Y %H:%M').dt.strftime('%Y-%m-%d %H:%M:%S')

# operation_logs: 确认op_time格式（原始已为标准格式）
operation_logs['op_time'] = pd.to_datetime(operation_logs['op_time']).dt.strftime('%Y-%m-%d %H:%M:%S')
